package school;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MySqlStudentDao implements StudentDao {

	@Override
	public int add(Student s) {
		String sql = "insert into `student`(sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass,last_exam) values (?,?,?,?,?,?,?,?,?,?,?,?)";
		return DBUtil.executeUpdate(sql, s.getSname(), s.getSex(),
				s.getTelephone(), s.getQq(), s.getWechat(), s.getGradeId(),
				s.getEnterdate(), s.getFromcity(), s.getBirthday(),
				s.getAddress(), s.getPass(), s.getLastexam());
	}

	@Override
	public int del(Long stuno) {
		String sql = "delete from student where stuno=?";
		return DBUtil.executeUpdate(sql, stuno);
	}

	@Override
	public int update(Student s) {
		String sql = "update student set sname=?,sex=?,telephone=?,qq=?,wechat=?,gradeid=?,enterdate=?,fromcity=?,birthday=?,address=?,pass=?,last_exam=? where stuno=?";
		return DBUtil.executeUpdate(sql, s.getSname(), s.getSex(),
				s.getTelephone(), s.getQq(), s.getWechat(), s.getGradeId(),
				s.getEnterdate(), s.getFromcity(), s.getBirthday(),
				s.getAddress(), s.getPass(), s.getLastexam(), s.getStuno());
	}

	@Override
	public Student find(Long stuno) {
		Student stu = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select * from `student` where stuno=?";
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			st = conn.prepareStatement(sql);
			st.setLong(1, stuno);
			rs = st.executeQuery();
			if (rs.next()) {
				stu = new Student();
				stu.setStuno(rs.getLong("stuno"));
				stu.setSname(rs.getString("sname"));
				stu.setSex(rs.getString("sex"));
				stu.setTelephone(rs.getString("telephone"));
				stu.setQq(rs.getString("qq"));
				stu.setWechat(rs.getString("wechat"));
				stu.setGradeId(rs.getLong("gradeid"));
				stu.setEnterdate(rs.getDate("enterdate"));
				stu.setFromcity(rs.getString("fromcity"));
				stu.setBirthday(rs.getDate("birthday"));
				stu.setAddress(rs.getString("address"));
				stu.setPass(rs.getString("pass"));
				stu.setLastexam(rs.getDate("last_exam"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, st, conn);
		}
		return stu;
	}

	@Override
	public List<Student> query() {
		List<Student> list = new ArrayList<Student>();
		Student stu = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select * from `student`";
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			st = conn.prepareStatement(sql);
			rs = st.executeQuery();
			while (rs.next()) {
				stu = new Student();
				stu.setStuno(rs.getLong("stuno"));
				stu.setSname(rs.getString("sname"));
				stu.setSex(rs.getString("sex"));
				stu.setTelephone(rs.getString("telephone"));
				stu.setQq(rs.getString("qq"));
				stu.setWechat(rs.getString("wechat"));
				stu.setGradeId(rs.getLong("gradeid"));
				stu.setEnterdate(rs.getDate("enterdate"));
				stu.setFromcity(rs.getString("fromcity"));
				stu.setBirthday(rs.getDate("birthday"));
				stu.setAddress(rs.getString("address"));
				stu.setPass(rs.getString("pass"));
				stu.setLastexam(rs.getDate("last_exam"));
				list.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, st, conn);
		}
		return list;
	}

	public List<Student> paging(int pageSize, int pageNo) {
		List<Student> list = new ArrayList<Student>();
		Student stu = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select * from `student` limit ?,?";
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			st = conn.prepareStatement(sql);
			st.setInt(1, pageSize * (pageNo - 1));
			st.setInt(2, pageSize);
			rs = st.executeQuery();
			while (rs.next()) {
				stu = new Student();
				stu.setStuno(rs.getLong("stuno"));
				stu.setSname(rs.getString("sname"));
				stu.setSex(rs.getString("sex"));
				stu.setTelephone(rs.getString("telephone"));
				stu.setQq(rs.getString("qq"));
				stu.setWechat(rs.getString("wechat"));
				stu.setGradeId(rs.getLong("gradeid"));
				stu.setEnterdate(rs.getDate("enterdate"));
				stu.setFromcity(rs.getString("fromcity"));
				stu.setBirthday(rs.getDate("birthday"));
				stu.setAddress(rs.getString("address"));
				stu.setPass(rs.getString("pass"));
				stu.setLastexam(rs.getDate("last_exam"));
				list.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, st, conn);
		}
		return list;
	}
}
